package pack1;

import java.awt.Container;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;

public class JDBCAllinOne implements ActionListener {
	
	JLabel lblFname,lblLname,lblf,lbll; 
	JLabel lblfVal,lbllVal; 
	JTextField item,price;   
	JButton btnInsert,btnDelete,btnClear;
	ResultSet rs ;
	
	private void createUI()
	{
		JFrame frame = new JFrame("Inventory");
		
		//Layout of Main Window
		Container c = frame.getContentPane();
		c.setLayout(new BoxLayout(c,BoxLayout.Y_AXIS));
		
		lblFname = new JLabel("Item description : ");
		lblLname = new JLabel("Price : ");
		item = new JTextField("",15);
		price = new JTextField();
	
		JPanel pnlInput = new JPanel(new GridLayout(2,2, 1, 3)); 
		
		pnlInput.add(lblFname);
		pnlInput.add(item);
		pnlInput.add(lblLname);
		pnlInput.add(price);
		
		
		btnInsert = new JButton("Insert"); 
		btnInsert.addActionListener(this);
		btnDelete = new JButton("Delete");
		btnDelete.addActionListener(this);
		btnClear = new JButton("Clear");
		btnClear.addActionListener(this);
		
		JPanel pnlButton = new JPanel(new GridLayout(1,1, 10, 20));
		
		pnlButton.add(btnInsert);
		pnlButton.add(btnDelete);
		pnlButton.add(btnClear);
		
		
		
		JPanel tablayout = new JPanel(new GridLayout(3,2, 1, 3));
		 JTable table = new JTable(new MyTableModel());
	        table.setPreferredScrollableViewportSize(new Dimension(800, 600));
	        table.setRowHeight(18);
	        JScrollPane scrollPane = new JScrollPane(table);

	        tablayout.setVisible(true);
		
		frame.add(pnlInput);
		frame.add(pnlButton);
		frame.add(scrollPane);
		frame.pack();
		frame.setVisible(true);
	}
	public void actionPerformed(ActionEvent evt) {		
		String cmd = evt.getActionCommand();
		
		if(cmd.equals("Insert"))
		{
			insertData();
		}else if(cmd.equals("Delete"))
		{
			deleteData();
		}else if(cmd.equals("Clear"))
		{
			clearControls();	
		}
	}
	
	private void insertData()
	{
		try
		{
			String sql = "Insert Into Inventory (Item,Price) " +
					"Values ('"+item.getText()+"','"+price.getText()+
					"')";
			Statement statement = DBconn.getConn().createStatement();
			statement.execute(sql);
			createMessageBox("Inserted Successfully");
			clearControls();
		}
		catch(Exception e)
		{
			System.out.println("Exception in JDBCAllinOne.insertData");
			createMessageBox(e.getMessage());
		}
	}
	private void deleteData()
	{
		try
		{
			System.out.println( item.getText() );
			String sql_where = "";
			int id  = 0;
			try{
				 id  = Integer.parseInt(item.getText()); 
			}catch(Exception e){}
			 System.out.println(id);
			
				 sql_where = "Item = '"+(String)item.getText() +"' OR ID = "+id+";"; 
				System.out.println( item.getText() );
			String sql = "delete from Inventory where  "+ sql_where ;
			System.out.println(sql);
			Statement statement = DBconn.getConn().createStatement();
			 int effect = statement.executeUpdate(sql);
			 if (effect > 0)
			createMessageBox("Record of "+item.getText()+" Deleted Successfully");
			clearControls();
			System.out.println("in delete.....");
		}
		catch(Exception e)
		{
			System.out.println("Exception in JDBCAllinOne.deleteData");
			createMessageBox(e.getMessage());
		}
	}
	
	private void createMessageBox(String msg)
	{
		JFrame frame = new JFrame("Result");
		JLabel lbl = new JLabel(msg);
		frame.add(lbl);
		frame.setSize(200,200);
		frame.setVisible(true);
	}
	private void clearControls()
	{
		String empty = "";
		item.setText(empty);
		price.setText(empty);
	}
	
	public static void main(String[] args) {
		JDBCAllinOne obj = new JDBCAllinOne();
		obj.createUI();
	}
}
